Bigtable にある位置情報を BigQuery Geo Viz で可視化してみた　～ IoT センサーから緯度経度データが送られてくるていで～

アドベントカレンダー2020

#Google BigQuery

#Google Cloud (GCP)

みかみ

2020.12.20

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、データアナリティクス事業本部のみかみです。

本エントリは、クラスメソッド BigQuery Advent Calendar 2020 の 20 日目のエントリです。 25日のアドベントカレンダー終了まで、弊社クラスメソッドデータアナリティクス事業本部のメンバーで、Google BigQuery に関する記事を紡いでいこうと思います。

やりたいこと

BigQuery から Bigtable データを参照するにはどうすればいいのか知りたい
Bigtable に格納している緯度経度データを BigQuery Geo Viz で可視化したい

IoT センサーから送られてくる緯度経度情報を、Bigtable に格納しているというシナリオを想定しました。

ため込んだ緯度経度データを SQL で分析したいのですが、NoSQL データベースである Bigtable では SQL を実行できません。さらに、緯度経度の数値だけだと分かりにくいので、どこの位置だか一目で分かるように地図上に地点をビジュアル表示できたらなおうれしい！ということで・・・。

Bigtable データを参照するための外部テーブルを BigQuery に作成し、SQL で緯度経度データを取得して BigQuery Geo Viz で可視化してみます。

Bigtable データを準備

BigQuery から参照する、Bigtable のデータを準備します。

以下のサイトから、都道府県の県庁所在の地緯度経度情報をいただいてきました。

都道府県庁所在地緯度経度データ｜みんなの知識ちょっと便利帳

2020/12 現在、BigQuery からの Bigtable データ参照がサポートされるリージョンは us-central1 と europe-west1 のみとのことです。東京（asia-northeast1）リージョンで試してみましたが、BigQuery の外部テーブル作成（後述）時にエラーになり、やはり実行できませんでした。

サポート対象リージョンとゾーン｜ BigQuery ドキュメント

ということで、us-central1 リージョンに Bigtable インスタンスを作成し、senser_latlon という名前のテーブルを作成して緯度経度データを書込みました。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ cbt read senser_latlon
2020/12/17 05:21:33 -creds flag unset, will use gcloud credential
----------------------------------------
r1
  cf1:lat                                  @ 2020/12/17-05:02:58.865000
    "43.06417"
  cf1:lon                                  @ 2020/12/17-05:03:09.669000
    "141.34694"
----------------------------------------
r10
  cf1:lat                                  @ 2020/12/17-05:07:02.032000
    "36.39111"
  cf1:lon                                  @ 2020/12/17-05:07:12.385000
    "139.06083"
----------------------------------------
（省略）
----------------------------------------
r9
  cf1:lat                                  @ 2020/12/17-05:06:42.552000
    "36.56583"
  cf1:lon                                  @ 2020/12/17-05:06:52.904000
    "139.88361"

47 都道府県の県庁所在地の緯度経度データが Bigtable に格納できました。

BigQuery の外部テーブルを作成

Bigtable のデータを BigQuery から参照するために、US リージョンのデータセットに外部テーブルを作成します。

外部の永続テーブルを使用して Cloud Bigtable データをクエリする｜ BigQuery ドキュメント

2020/12 現在、BigQuery 管理コンソールから Bigtable の外部テーブルを作成することはできないので、Cloud Shell から bq コマンドを実行してテーブルを作成します。

以下のテーブル定義ファイルを準備しました。

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/cm-da-mikami-yuki-258308/instances/advent-calendar/tables/senser_latlon"
    ],
    "bigtableOptions": {
        "readRowkeyAsString": "true",
        "columnFamilies" : [
            {
                "familyId": "cf1",
                "type": "STRING",
                "encoding": "TEXT"
            }
        ]
    }
}

Cloud Bigtable テーブル定義を作成する｜ BigQuery ドキュメント

bq mk コマンドでテーブル定義ファイルを指定して実行します。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq mk --external_table_definition=latlon.txt dataset_advent_calendar_us.latlon
Table 'cm-da-mikami-yuki-258308:dataset_advent_calendar_us.latlon' successfully created.

無事、外部テーブルが作成できました。

ちゃんとデータが参照できるかどうか確認してみます。

テーブルスキーマでフィールドのタイプが RECORD になっていた通り、Bigtable の列ファミリー（ cf1 ）ごとにネスト構造になったデータが取得できました。

必要なのは cf1.column.cell.value に入っている緯度経度情報なので、以下のクエリで取得してみます。

WITH latitude as (
  SELECT rowkey, value FROM (
  SELECT rowkey, name, cell FROM dataset_advent_calendar_us.latlon, UNNEST(cf1.column)
), UNNEST(cell)
WHERE
  name = 'lat'
),
londitude as (
  SELECT rowkey, value FROM (
  SELECT rowkey, name, cell FROM dataset_advent_calendar_us.latlon, UNNEST(cf1.column)
), UNNEST(cell)
WHERE
  name = 'lon'
)
SELECT
  CAST(lat.value AS FLOAT64) as lat,
  CAST(lon.value AS FLOAT64) as lon
FROM
  latitude as lat
  INNER JOIN londitude as lon ON lat.rowkey = lon.rowkey
LIMIT 10

緯度経度データも正常に取得できることが確認できました。

BigQuery Geo Viz で緯度経度データを可視化

BigQuery Geo Viz を使うと、ブラウザから GCP の対象プロジェクトを選択しクエリエディタに SQL を入力して実行するだけで、取得した位置情報を地図上にプロット表示することができます。

本シリーズ BigQuery Advent Calendar 2020 の 3 日目の記事でもご紹介しているので、ぜひご覧ください。

BigQuery Geo Viz から実行するのは以下のクエリです。

WITH latitude as (
  SELECT rowkey, value FROM (
  SELECT rowkey, name, cell FROM dataset_advent_calendar_us.latlon, UNNEST(cf1.column)
), UNNEST(cell)
WHERE
  name = 'lat'
),
londitude as (
  SELECT rowkey, value FROM (
  SELECT rowkey, name, cell FROM dataset_advent_calendar_us.latlon, UNNEST(cf1.column)
), UNNEST(cell)
WHERE
name = 'lon'
)
SELECT
  ST_GeogPoint(lon, lat) AS WKT
FROM (
  SELECT
    CAST(lat.value AS FLOAT64) as lat,
    CAST(lon.value AS FLOAT64) as lon
  FROM
    latitude as lat
    INNER JOIN londitude as lon ON lat.rowkey = lon.rowkey
)

エディタに SQL を入力して、「Run」ボタンで実行しました。

よくよく目をこらすと地図上に小さな点が表示されているようないないような。。

「Show Results」ボタンで結果を確認みると、ちゃんと 47 都道府県の位置が取得できているようです。

「Add Styles」ボタンから「fillColor」と「circleRadius」の項目で、点の色と大きさを調整します。

可視化の形式設定｜ BigQuery ドキュメント

「Apply Style」ボタンで変更を適用してみると

今度ははっきりと地図上で位置が確認できるようになりました！

まとめ（所感）

BigQuery の外部テーブル作成時に指定するテーブル定義ファイルの準備に少し手間取りましたが、他は特に迷うことなく、Bigtable のデータを BigQuery から参照することができました。現在のところ Bigtable データに対するクエリ実行には、管理コンソールからの外部テーブル作成やスキーマの自動検出ができなかったり、リージョン制限があったりと、少し使いにくい印象ですが、 Cloud Shell を使えば gcloud コマンドをすぐに実行できますし、一度やってみるとすぐに慣れると思います。今後 GCS や Google ドライブファイルへのクエリのように、管理コンソールの UI 操作＆スキーマ自動検出で Bigtable の外部テーブルも作成できるようになるとなおうれしいですねｖ

SQL ということばでひたすらデータと会話する若干地味なデータ分析という作業ですが、ビジュアライズされた結果が見れるとやはりうれしいものだと思います。 BigQuery Geo Viz、初めてさわってみましたが、面倒な設定や準備など一切必要なく、画面から SQL を実行するだけで位置データを直感的に分かりやすく可視化してくれるので、合わせて使えばより便利だと思いました！

明日 21 日目の BigQuery Advent Calendar 2020 はしんやからお送り予定です。また、今後のアドベントカレンダーの予定はこちらにも掲載しておりますので、引き続きお楽しみいただけますと幸いです。